package com.ms.qw.infraestructura.persistence.jpa.sigar;

import com.ms.qw.client.dto.ComiteDTO;
import com.ms.qw.client.dto.ProgramacionDTO;
import com.ms.qw.client.dto.ProgramacionFlt;
import com.ms.qw.client.dto.RacionDetalleDTO;
import com.ms.qw.domain.model.Programacion;
import com.ms.qw.domain.model.ProgramacionRepository;
import com.ms.qw.domain.model.Receta;
import com.ms.qw.domain.model.RecetaRepository;
import com.ms.qw.server.infrastructure.mapper.MSResultTransformer;
import com.ms.qw.server.infrastructure.persistence.util.InClauseConstructor;
import com.ms.qw.server.infrastructure.persistence.util.ValueProvider;
import com.ms.support.jpa.BaseDAO;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.impl.SQLQueryImpl;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.PostConstruct;
import javax.sql.DataSource;
import java.text.SimpleDateFormat;
import java.util.List;


/**
 * User: aglwkrs
 * Date: 30/11/11
 */
@Repository
public class ProgramacionRepositoryImpl extends BaseDAO<Programacion, Integer> implements ProgramacionRepository {

    private final static Logger logger = LoggerFactory.getLogger(ProgramacionRepositoryImpl.class);

    private JdbcTemplate jdbcTemplate;
    private NamedParameterJdbcTemplate namedParameterjdbcTemplate;


    @Autowired
    DataSource dataSource;

    @PostConstruct
    public void init() {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
        this.namedParameterjdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
    }

    public Integer total(ProgramacionFlt programacionFlt) {
        String comiteIn = InClauseConstructor.getInClause(
                programacionFlt.getComiteList(), new ValueProvider() {
            public Object getValue(Object obj) {
                return "'" + ((ComiteDTO) obj).getCodigo() + "'";
            }
        }
        );


        StringBuffer sql = new StringBuffer();
        sql.append("SELECT     count(*) ");
        sql.append("FROM       EQSIGEPSA.PRMAEPROGRAMACION PMP ");
        sql.append("           INNER JOIN EQGENERAL.GEMAECAMPANA GMC ");
        sql.append("           ON         GMC.iCodCampana=PMP.iCodCampana ");
        sql.append("           INNER JOIN EQGENERAL.GETABCOMITECOMPRA GCC ");
        sql.append("           ON         GCC.cCodComite =PMP.cCodComite ");
        sql.append("           INNER JOIN EQSIGEPSA.PRTABRACION PR ");
        sql.append("           ON         PR.iCodRacion = PMP.iCodRacion");
        sql.append("           WHERE 1 = 1 ");

        if (programacionFlt.getComite() != null) {
            sql.append("       AND  PMP.[cCodComite] = '" + programacionFlt.getComite().getCodigo() + "'");
        }

        if(StringUtils.isNotBlank(comiteIn)){
            sql.append("       AND  PMP.[cCodComite] in " + comiteIn + " ");
        }

        if (programacionFlt.getCampana() != null) {
            sql.append("       AND  PMP.[iCodCampana] = '" + programacionFlt.getCampana().getCodigo() + "'");
        }

        if (programacionFlt.getFechaInicio()!=null && programacionFlt.getFechaFin()!=null){
            SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
            sql.append("   AND CONVERT(VARCHAR(10), PMP.dFecCreacion, 105)  >=  '"+sdf.format(programacionFlt.getFechaInicio()) +"' \n" +
                    "        AND CONVERT(VARCHAR(10), PMP.dFecCreacion, 105)<=  '"+sdf.format(programacionFlt.getFechaFin())+"' ");
        }
        return jdbcTemplate.queryForInt(sql.toString());
    }

    public List<ProgramacionDTO> find(ProgramacionFlt programacionFlt) {
        String comiteIn = InClauseConstructor.getInClause(
                programacionFlt.getComiteList(), new ValueProvider() {
            public Object getValue(Object obj) {
                return "'" + ((ComiteDTO) obj).getCodigo() + "'";
            }
        }
        );

        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT  *\n" +
                "        FROM    (");
        sql.append(" SELECT ROW_NUMBER() OVER ( ORDER BY PMP.dFecCreacion desc) AS RowNum,");
        sql.append("           PMP.[iCodProgramacion] AS \"codigo\"       , ");
        sql.append("           PMP.[iCodRacion]       AS \"racion.codigo\" , ");
        sql.append("           PR.vRacion             AS \"racion.nombre\" , ");
        sql.append("           PR.iCodClaseRacion AS \"racion.codigoClaseRacion\" , ");
        sql.append("           PR.iCodTipoRacion AS \"racion.codigoTipoRacion\" , ");
        sql.append("           PMP.[cCodComite]       AS \"comite.codigo\" , ");
        sql.append("           GCC.vComite            AS \"comite.nombre\" , ");
        sql.append("           PMP.[iCodCampana]      AS \"campana.codigo\", ");
        sql.append("           GMC.vCampana           AS \"campana.nombre\", ");
        sql.append("           PMP.cCreadoPor           AS \"creadoPor\", ");
        sql.append("           PMP.dFecCreacion           AS \"fechaCreacion\", ");
        sql.append("           PMP.[cEstado]          AS \"estado\" ");
        sql.append("FROM       EQSIGEPSA.PRMAEPROGRAMACION PMP ");
        sql.append("           INNER JOIN EQGENERAL.GEMAECAMPANA GMC ");
        sql.append("           ON         GMC.iCodCampana=PMP.iCodCampana ");
        sql.append("           INNER JOIN EQGENERAL.GETABCOMITECOMPRA GCC ");
        sql.append("           ON         GCC.cCodComite =PMP.cCodComite ");
        sql.append("           INNER JOIN EQSIGEPSA.PRTABRACION PR ");
        sql.append("           ON         PR.iCodRacion = PMP.iCodRacion");
        sql.append("           WHERE 1 = 1 ");

        if (programacionFlt.getComite() != null) {
            sql.append("       AND  PMP.[cCodComite] = '" + programacionFlt.getComite().getCodigo() + "'");
        }
        if (programacionFlt.getCampana() != null) {
            sql.append("       AND  PMP.[iCodCampana] = '" + programacionFlt.getCampana().getCodigo() + "'");
        }
        if(StringUtils.isNotBlank(comiteIn)){
            sql.append("       AND  PMP.[cCodComite] in " + comiteIn + " ");
        }

        if (programacionFlt.getFechaInicio()!=null && programacionFlt.getFechaFin()!=null){
            SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
            sql.append("   AND CONVERT(VARCHAR(10), PMP.dFecCreacion, 105)  >=  '"+sdf.format(programacionFlt.getFechaInicio()) +"' \n" +
                    "        AND CONVERT(VARCHAR(10), PMP.dFecCreacion, 105)<=  '"+sdf.format(programacionFlt.getFechaFin())+"' ");
        }
        sql.append(" ) AS T where");
        sql.append("    T.RowNum > " + programacionFlt.getStart());
        sql.append("  AND " + programacionFlt.getEnd() + ">= T.RowNum ");
        sql.append("  ORDER BY T.RowNum ");

        org.hibernate.Query query = em().unwrap(Session.class)
                .createSQLQuery(sql.toString())
                .setResultTransformer(new MSResultTransformer(ProgramacionDTO.class));
        return query.list();
    }

}
